﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Entities;
using Common.Helper;
using Common.Constants;

namespace DataAccess
{
    public class OrganizationDataAccess
    {
        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>
        /// The instance.
        /// </value>
        public static OrganizationDataAccess Instance { get; private set; }

        /// <summary>
        /// Initializes the <see cref="AddressDataAccess" /> class.
        /// </summary>
        static OrganizationDataAccess()
        {
            Instance = new OrganizationDataAccess();
        }

        /// <summary>
        /// Extracts the raw data to Organization entity
        /// </summary>
        /// <param name="dataRecord">The data record.</param>
        /// <returns></returns>
        private Organisation ExtractRawData(IDataRecord dataRecord)
        {
            var organisation = new Organisation();
            bool isActive;
            if (bool.TryParse(dataRecord["IsActive"].ToString(), out isActive))
                organisation.IsActive = isActive;
            organisation.OrgId = (Guid)dataRecord["OrgId"];
            organisation.OrgName = dataRecord["OrgName"].ToString();
            organisation.ShortDescription = dataRecord["ShortDescription"].ToString();
            organisation.ContactId = dataRecord["ContactId"].ToString();
            organisation.AddressLine1 = dataRecord["AddressLine1"].ToString();
            organisation.AddressLine2 = dataRecord["AddressLine2"].ToString();
            organisation.AddressLine3 = dataRecord["AddressLine2"].ToString();
            bool orgPreferred;
            if (bool.TryParse(dataRecord["OrgPreferred"].ToString(), out orgPreferred))
                organisation.OrgPreferred = orgPreferred;

            bool expressionOfBusiness;
            if (bool.TryParse(dataRecord["ExpressionOfBusiness"].ToString(), out expressionOfBusiness))
                organisation.ExpressionOfBusiness = expressionOfBusiness;
            organisation.BusinessId = (Guid)dataRecord["BusinessId"];
            organisation.AddressId = (Guid)dataRecord["AddressId"];
            organisation.OrgFullDescription = dataRecord["OrgFullDescription"].ToString();
            organisation.PhoneNumber = dataRecord["PhoneNumber"].ToString();
            organisation.Fax = dataRecord["Fax"].ToString();
            organisation.Email = dataRecord["Email"].ToString();
            organisation.WebAddress = dataRecord["WebAddress"].ToString();
            organisation.CharityNumber = dataRecord["CharityNumber"].ToString();
            organisation.CompanyNumber = dataRecord["CompanyNumber"].ToString();

            organisation.Specialism = dataRecord["Specicalism"].ToString();

            organisation.ServicePersonalCircumstances = dataRecord["ServicePersonalCircumstances"].ToString();

            organisation.ServiceDisability = dataRecord["ServiceDisablities"].ToString();


            organisation.ServiceEthnicity = dataRecord["ServiceEthnicity"].ToString();


            organisation.ServiceBarriers = dataRecord["ServiceBarriers"].ToString();


            organisation.Accreditation = dataRecord["Accreditation"].ToString();


            organisation.ServiceBenefits = dataRecord["ServiceBenefits"].ToString();


            organisation.EoiProgrammes = dataRecord["EoiProgrammes"].ToString();

            organisation.EoiService = dataRecord["EOIServices"].ToString();
            organisation.Roles = dataRecord["Roles"].ToString();

            return organisation;

        }

        #region add, amend Organization by HieuTT5

        /// <summary>
        /// Inserts the organisation.
        /// </summary>
        /// <param name="organization">The organization.</param>
        /// <returns></returns>
        public int InsertOrganisation(Organisation organization)
        {
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("OrgId", organization.OrgId),
                                       new SqlParameter("OrgName", organization.OrgName),
                                       new SqlParameter("ShortDescription", organization.ShortDescription),
                                       new SqlParameter("Contactid", organization.ContactId),
                                       new SqlParameter("AddressLine1", organization.AddressLine1),
                                       new SqlParameter("AddressLine2", organization.AddressLine2),
                                       new SqlParameter("AddressLine3", organization.AddressLine3),
                                       new SqlParameter("AddressId", organization.AddressId),
                                       new SqlParameter("OrgPreferred", organization.OrgPreferred),
                                       new SqlParameter("ExpressionOfBusiness", organization.ExpressionOfBusiness),
                                       new SqlParameter("BusinessId", organization.BusinessId),
                                       new SqlParameter("OrgFullDescription", organization.OrgFullDescription),
                                       new SqlParameter("PhoneNumber", organization.PhoneNumber),
                                       new SqlParameter("Fax", organization.Fax),
                                       new SqlParameter("Email", organization.Email),
                                       new SqlParameter("WebAddress", organization.WebAddress),
                                       new SqlParameter("CharityNumber", organization.CharityNumber),
                                       new SqlParameter("CompanyNumber", organization.CompanyNumber),
                                       new SqlParameter("Specicalism", organization.Specialism),
               
                                       new SqlParameter("ServicePersonalCircumstances",
                                                        organization.ServicePersonalCircumstances),
                                       new SqlParameter("ServiceDisablities", organization.ServiceDisability),
                                       new SqlParameter("ServiceEthnicit", organization.ServiceEthnicity),
                                       new SqlParameter("ServiceBarriers", organization.ServiceBarriers),
                                       new SqlParameter("Accreditation", organization.Accreditation),
                                       new SqlParameter("ServiceBenefits", organization.ServiceBenefits),
                                       new SqlParameter("EOIProgrammes", organization.EoiProgrammes),
                                       new SqlParameter("EOIServices", organization.EoiService),
                                       new SqlParameter("IsActive", organization.IsActive),
                                       new SqlParameter("Roles",organization.Roles)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.AddOrganization, colParameter);
            return result;
        }

        /// <summary>
        /// Updates the organization.
        /// </summary>
        /// <param name="organization">The organization.</param>
        /// <returns></returns>
        public int UpdateOrganization(Organisation organization)
        {
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("OrgId", organization.OrgId),
                                       new SqlParameter("OrgName", organization.OrgName),
                                       new SqlParameter("ShortDescription", organization.ShortDescription),
                                       new SqlParameter("Contactid", organization.ContactId),
                                       new SqlParameter("AddressLine1", organization.AddressLine1),
                                       new SqlParameter("AddressLine2", organization.AddressLine2),
                                       new SqlParameter("AddressLine3", organization.AddressLine3),
                                       new SqlParameter("AddressId", organization.AddressId),
                                       new SqlParameter("OrgPreferred", organization.OrgPreferred),
                                       new SqlParameter("ExpressionOfBusiness", organization.ExpressionOfBusiness),
                                       new SqlParameter("BusinessId", organization.BusinessId),
                                       new SqlParameter("OrgFullDescription", organization.OrgFullDescription),
                                       new SqlParameter("PhoneNumber", organization.PhoneNumber),
                                       new SqlParameter("Fax", organization.Fax),
                                       new SqlParameter("Email", organization.Email),
                                       new SqlParameter("WebAddress", organization.WebAddress),
                                       new SqlParameter("CharityNumber", organization.CharityNumber),
                                       new SqlParameter("CompanyNumber", organization.CompanyNumber),
                                         new SqlParameter("Specicalism", organization.Specialism),
               
                                       new SqlParameter("ServicePersonalCircumstances",
                                                        organization.ServicePersonalCircumstances),
                                       new SqlParameter("ServiceDisablities", organization.ServiceDisability),
                                       new SqlParameter("ServiceEthnicity", organization.ServiceEthnicity),
                                       new SqlParameter("ServiceBarriers", organization.ServiceBarriers),
                                       new SqlParameter("Accreditation", organization.Accreditation),
                                       new SqlParameter("ServiceBenefits", organization.ServiceBenefits),
                                       new SqlParameter("EOIProgrammes", organization.EoiProgrammes),
                                       new SqlParameter("EOIServices", organization.EoiService),
                                       new SqlParameter("IsActive", organization.IsActive)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.UpdateOrganization, colParameter);
            return result;
        }



        /// <summary>
        /// Gets the name of the organisation by.
        /// </summary>
        /// <param name="organizationName">Name of the organization.</param>
        /// <returns></returns>
        public Organisation GetOrganisationByName(string organizationName)
        {
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("orgName", organizationName),
                                   };
            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.GetOrganizationByName, colParameter))
            {
                if (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    return result;
                }
                return null;
            }
        }



        /// <summary>
        /// Gets the organisation by id.
        /// </summary>
        /// <param name="organisationId">The organisation id.</param>
        /// <returns></returns>
        public Organisation GetOrganisationById(string organisationId)
        {
            var colParamter = new Collection<SqlParameter>
                                  {
                                      new SqlParameter("OrgId", organisationId)
                                  };
            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.GetOrganizationById, colParamter))
            {
                if (reader.Read())
                {
                    return ExtractRawData(reader);
                }
                return null;
            }

        }


        #endregion

        #region list Organization by ThinhDV

        /// <summary>
        /// Lists the organization.
        /// </summary>
        /// <param name="includeInactive">if set to <c>true</c> [include inactive].</param>
        /// <returns></returns>
        public Collection<Organisation> ListOrganization(bool includeInactive)
        {

            var lstOrganisation = new Collection<Organisation>();
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("isActive", includeInactive)
                                   };
            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.ListOrganisation, colParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    lstOrganisation.Add(result);
                }
                return lstOrganisation;

            }
        }

        /// <summary>
        /// Marks the inactive.
        /// </summary>
        /// <param name="orgId">The org id.</param>
        /// <returns></returns>
        public int MarkInactive(string orgId)
        {
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("orgId", orgId)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.OrganisationMarkInactive, colParameter);
            return result;

        }
        /// <summary>
        /// Organisations the order by contact.
        /// </summary>
        /// <param name="modeOrder">if set to <c>true</c> [mode order].</param>
        /// <returns></returns>
        public Collection<Organisation> OrganisationOrderByContact(bool modeOrder)

        {
            var lstOrganisation = new Collection<Organisation>();
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("@orderMode", modeOrder)
                                   };

            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.OrganisationOrderByContact, colParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    lstOrganisation.Add(result);
                }
                return lstOrganisation;

            }
        }
        /// <summary>
        /// Organisations the order by address.
        /// </summary>
        /// <param name="modeOrder">if set to <c>true</c> [mode order].</param>
        /// <returns></returns>
        public Collection<Organisation> OrganisationOrderByAddress(bool modeOrder)
        {
            var lstOrganisation = new Collection<Organisation>();
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("@orderMode", modeOrder)
                                   };

            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.OrganisationOrderByAddress, colParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    lstOrganisation.Add(result);
                }
                return lstOrganisation;

            }
        }
        #endregion

        /// <summary>
        /// Edits the roles.
        /// </summary>
        /// <param name="orgId">The org id.</param>
        /// <param name="roles">The roles.</param>
        /// <returns></returns>
        public int EditRoles(Guid orgId,string roles)
        {
            if (orgId == Guid.Empty)
            {
                return -1;
            }
            if (roles == null)
            {
                return -1;
            }
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("OrgID", orgId),
                                       new SqlParameter("Roles", roles)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.EditRoleOrganisation, colParameter);
            return result;
        }
    }
}
